﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using Antu.WebControls;
using Antu.Core.Components;
using Antu.Core.Services;
using System.Web.UI.WebControls;
using EluZSProvider;
using System.Web;
using System.Data;

namespace WebSys.UI.Control.ZhengShou
{
    public class CX_YJJGControl : MPBasicTemplate
    {
        EluZSDataProvider _EluZSDataProvider = (EluZSDataProvider)ProviderService.ProviderServiceMange.GetProvider("EluZSProvider");
        Literal 安置房源面积小于被征收面积, 补偿价款已严重超过此区域房屋均价, 补偿装修费用已超过预警值, 余额已低于警戒值, 账户余额为零, 存在部分协议补偿款未支付, 到账余额已小于所有协议所对应的补偿额度, 补偿总价小于评估总价, 补偿总价超出评估总价, 协议补偿款未支付;
        protected override void OnLoad(EventArgs e)
        {
            InitControl();
            if (!base.Page.IsPostBack)
            {
                BindData();
            }
        }
        protected void InitControl() {
            安置房源面积小于被征收面积 = (Literal)FindControl("安置房源面积小于被征收面积");
            补偿价款已严重超过此区域房屋均价 = (Literal)FindControl("补偿价款已严重超过此区域房屋均价");
            补偿装修费用已超过预警值 = (Literal)FindControl("补偿装修费用已超过预警值");
            余额已低于警戒值 = (Literal)FindControl("余额已低于警戒值");
            账户余额为零 = (Literal)FindControl("账户余额为零");

            存在部分协议补偿款未支付 = (Literal)FindControl("存在部分协议补偿款未支付");
            到账余额已小于所有协议所对应的补偿额度 = (Literal)FindControl("到账余额已小于所有协议所对应的补偿额度");
            补偿总价小于评估总价 = (Literal)FindControl("补偿总价小于评估总价");
            补偿总价超出评估总价 = (Literal)FindControl("补偿总价超出评估总价");
            //协议补偿款未支付 = (Literal)FindControl("协议补偿款未支付");
        }
        protected void BindData()
        {
            安置房源面积小于被征收面积.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When p.建筑面积<z.房屋建筑面积 Then 1 Else 0 End)
from zs_proloupan p,zs_zsperson z where p.zspid=z.id ");
            补偿价款已严重超过此区域房屋均价.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When 
(select (to_number(nvl(replace(TEXTBOX19,'\',''),0))+
(to_number(nvl(replace(TEXTBOX40,'\',''),0))+
to_number(nvl(replace(TEXTBOX48,'\',''),0))+
to_number(nvl(replace(TEXTBOX51,'\',''),0))+
to_number(nvl(replace(TEXTBOX37,'\',''),0)))) 协议款 from zs_hbhxy
Where personid=t.personid And pnid=t.pnid)
>
((select sum(to_number(nvl(replace(ZZ评估总价, '\', ''),0))  +to_number(nvl(replace(YY评估总价, '\', ''),0))+to_number(nvl(replace(FYY评估总价, '\', ''),0))) 区域房屋均价 
from zs_zzbcjfenhu Where zspid=t.personid And pid=t.pnid)
*(Select nvl(补偿装修费用预警值,0)/100 From zs_yjcs)) 
Then 1 Else 0 End ) 
From zs_hbhxy t");
            补偿装修费用已超过预警值.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When 
(select (to_number(nvl(replace(TEXTBOX19,'\',''),0))+
(to_number(nvl(replace(TEXTBOX40,'\',''),0))+
to_number(nvl(replace(TEXTBOX48,'\',''),0))+
to_number(nvl(replace(TEXTBOX51,'\',''),0))+
to_number(nvl(replace(TEXTBOX37,'\',''),0)))) 协议款 from zs_hbhxy
Where personid=t.personid And pnid=t.pnid)
>
((Select (sum(to_number(nvl(replace(zz产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zz承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(yy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(yy承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(fyy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(fyy承租人补偿款, '\', ''), 0))) +
       sum(to_number(nvl(replace(zs产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zs承租人补偿款, '\', ''), 0)))) 评估款 
from zs_zzbcjfenhu Where zspid=t.personid And pid=t.pnid)
*(Select nvl(补偿装修费用预警值,0)/100 From zs_yjcs)) 
Then 1 Else 0 End ) 
From zs_hbhxy t");
            余额已低于警戒值.Text = _EluZSDataProvider.GetString(@"Select 
Sum(Case When ((select to_number(nvl((select sum(to_number(nvl(replace(t.TEXTBOX19, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX40, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX48, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX51, '\', ''), 0)) +
                   to_number(nvl(replace(t.TEXTBOX37, '\', ''), 0))) hb
          from zs_hbhxy t
         where t.pnid in (select id from zs_prono where pid=p.pnid) ),0)) +
       to_number(nvl((select sum(to_number(nvl(replace(z.TEXTBOX21, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX38, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX41, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX49, '\', ''), 0)) +
                   to_number(nvl(replace(z.TEXTBOX52, '\', ''), 0))) cq
          from zs_cqzhxy z
         where z.fid in
               (select id
                  from zs_proloupan z1
                 where z1.pid in (select id from zs_prono where pid=p.pnid))),0)) 款 from dual)*
                 (Select nvl(余额警戒值,0)/100 From zs_yjcs))     
>
(Select (To_Number(Nvl((Select Sum(To_Number(Nvl(Replace(到账金额,
                                                               '\',
                                                               ''),
                                                       0)))
                                From Zs_New_Money
                               Where Bid = p.Id),
                              0)) - To_Number(Nvl((Select Sum(To_Number(Nvl(Replace(划转金额,
                                                                                    '\',
                                                                                    ''),
                                                                            0)))
                                                     From Zs_Change_Money
                                                    Where Bid = p.Id),
                                                   0)))
          From Dual) Then 1 Else 0 End ) newsum
From Zs_Bcbasicinfo p ");
            账户余额为零.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When (Select (To_Number(Nvl((Select Sum(To_Number(Nvl(Replace(到账金额,'\',''),0))) From Zs_New_Money Where Bid = p.Id),0)) - To_Number(Nvl((Select Sum(To_Number(Nvl(Replace(划转金额,'\',''),0)))
From Zs_Change_Money Where Bid = p.Id),0)))From Dual)=0 Then 1 Else 0 End ) From Zs_Bcbasicinfo p");

            存在部分协议补偿款未支付.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When (select sum(replace(到账金额, '\', '')) from zs_bcbasicinfo Where pnid =t.pnid) >(select sum(replace(划转金额, '\', '')) from zs_bcbasicinfo Where pnid =t.pnid) Then 1 Else 0 End ) From zs_bcbasicinfo t ");
            到账余额已小于所有协议所对应的补偿额度.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When 
(select sum(to_number(nvl(replace(ZZ承租人补偿款, '\', ''), 0)) +
to_number(nvl(replace(ZZ产权人补偿款, '\', ''), 0)) +
to_number(nvl(replace(YY承租人补偿款, '\', ''), 0)) +
to_number(nvl(replace(YY产权人补偿款, '\', ''), 0)) +
to_number(nvl(replace(FYY承租人补偿款, '\', ''), 0)) +
to_number(nvl(replace(FYY产权人补偿款, '\', ''), 0)) +
to_number(nvl(replace(ZS承租人补偿款, '\', ''), 0)) +
to_number(nvl(replace(ZS产权人补偿款, '\', ''), 0))) 需划转额度
from zs_zzbcjfenhu Where pid In (Select Id From zs_prono Where pid =t.pnid)) >
(select sum(replace(到账金额, '\', '')) from zs_bcbasicinfo Where pnid =t.pnid)
Then 1 Else 0 End )
From zs_bcbasicinfo t ");
            补偿总价小于评估总价.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When ((select to_number(nvl(Sum(to_number(nvl(replace(TEXTBOX19,'\',''),0))+
(to_number(nvl(replace(TEXTBOX40,'\',''),0))+
to_number(nvl(replace(TEXTBOX48,'\',''),0))+
to_number(nvl(replace(TEXTBOX51,'\',''),0))+
to_number(nvl(replace(TEXTBOX37,'\',''),0)))),0)) from zs_hbhxy
Where personid=t.id)+
(Select to_number(nvl(Sum(
to_number(nvl(replace(textbox19, '\', '0'), 0)) +
to_number(nvl(replace(textbox38, '\', '0'), 0))+
to_number(nvl(replace(textbox41, '\', '0'), 0)) +
to_number(nvl(replace(textbox49, '\', '0'), 0)) +
to_number(nvl(replace(textbox52, '\', '0'), 0))),0))
From zs_cqzhxy Where fid In (Select Id From zs_proloupan Where zspid=t.id)))<
(Select to_number(nvl(sum(to_number(nvl(replace(zz产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zz承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(yy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(yy承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(fyy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(fyy承租人补偿款, '\', ''), 0))+
       to_number(nvl(replace(zs产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zs承租人补偿款, '\', ''), 0))),0))  
from zs_zzbcjfenhu Where zspid=t.id) Then 1 Else 0 End )
From zs_zsperson t ");
            补偿总价超出评估总价.Text = _EluZSDataProvider.GetString(@"Select Sum(Case When ((select to_number(nvl(Sum(to_number(nvl(replace(TEXTBOX19,'\',''),0))+
(to_number(nvl(replace(TEXTBOX40,'\',''),0))+
to_number(nvl(replace(TEXTBOX48,'\',''),0))+
to_number(nvl(replace(TEXTBOX51,'\',''),0))+
to_number(nvl(replace(TEXTBOX37,'\',''),0)))),0)) from zs_hbhxy
Where personid=t.id)+
(Select to_number(nvl(Sum(
to_number(nvl(replace(textbox19, '\', '0'), 0)) +
to_number(nvl(replace(textbox38, '\', '0'), 0))+
to_number(nvl(replace(textbox41, '\', '0'), 0)) +
to_number(nvl(replace(textbox49, '\', '0'), 0)) +
to_number(nvl(replace(textbox52, '\', '0'), 0))),0))
From zs_cqzhxy Where fid In (Select Id From zs_proloupan Where zspid=t.id)))>
(Select to_number(nvl(sum(to_number(nvl(replace(zz产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zz承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(yy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(yy承租人补偿款, '\', ''), 0)) +
       to_number(nvl(replace(fyy产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(fyy承租人补偿款, '\', ''), 0))+
       to_number(nvl(replace(zs产权人补偿款, '\', ''), 0)) + to_number(nvl(replace(zs承租人补偿款, '\', ''), 0))),0))  
from zs_zzbcjfenhu Where zspid=t.id) Then 1 Else 0 End )
From zs_zsperson t ");
            //协议补偿款未支付.Text = "";
        }
    }
}
